package com.zl.learning.db.sqlite.core;

import org.apache.log4j.Logger;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * Created by zhaolei on 2017/5/23.
 */
public class JdbcTemplate implements JdbcOperations{
    Logger logger = Logger.getLogger(JdbcTemplate.class);
    private DataSource dataSource;

    public JdbcTemplate() {
    }

    public JdbcTemplate(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public <T> List<T> queryForList(String sql, RowMapper<T> rowMapper) throws Exception {
        return query(sql, new RowMapperResultSetExtractor<T>(rowMapper));
    }

    @Override
    public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws Exception {
        if (logger.isDebugEnabled()) {
            logger.debug("Executing SQL query [" + sql + "]");
        }
        class QueryStatementCallback implements StatementCallback<T> {
            @Override
            public T doInStatement(Statement stmt) throws Exception {
                ResultSet rs = null;
                try {
                    rs = stmt.executeQuery(sql);
                    ResultSet rsToUse = rs;
                    return rse.extractData(rsToUse);
                }
                finally {
                    JdbcUtils.closeResultSet(rs);
                }
            }
        }
        return execute(new QueryStatementCallback());
    }

    @Override
    public <T> T execute(StatementCallback<T> action) throws Exception {
        Connection con = getConnection(getDataSource());
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            Statement stmtToUse = stmt;
            T result = action.doInStatement(stmtToUse);
            return result;
        }catch (SQLException ex) {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            releaseConnection(con, getDataSource());
            con = null;
        }finally {
            JdbcUtils.closeStatement(stmt);
            releaseConnection(con, getDataSource());
        }
        return null;
    }

    @Override
    public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws Exception {
        List<T> results = queryForList(sql, rowMapper);
        return JdbcUtils.requiredSingleResult(results);
    }

    @Override
    public <T> T queryForObject(String sql, Class<T> requiredType) throws Exception {
        return queryForObject(sql, getSingleColumnRowMapper(requiredType));
    }

    @Override
    public Map<String, Object> queryForMap(String sql) throws Exception {
        return queryForObject(sql, getColumnMapRowMapper());
    }

    @Override
    public <T> List<T> queryForList(String sql, Class<T> elementType) throws Exception {
        return queryForList(sql, getSingleColumnRowMapper(elementType));
    }

    @Override
    public List<Map<String, Object>> queryForList(String sql) throws Exception {
        return queryForList(sql, getColumnMapRowMapper());
    }

    @Override
    public int update(String sql) throws Exception {
        if (logger.isDebugEnabled()) {
            logger.debug("Executing SQL update [" + sql + "]");
        }
        class UpdateStatementCallback implements StatementCallback<Integer>{
            @Override
            public Integer doInStatement(Statement stmt) throws SQLException {
                int rows = stmt.executeUpdate(sql);
                if (logger.isDebugEnabled()) {
                    logger.debug("SQL update affected " + rows + " rows");
                }
                return rows;
            }
        }
        return execute(new UpdateStatementCallback());
    }

    public static Connection getConnection(DataSource dataSource){
        Connection conn = null;
        try {
            dataSource = getDataSource();
            conn = dataSource.getConnection();
        }catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void releaseConnection(Connection conn, DataSource dataSource){
        try {
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static DataSource getDataSource() throws Exception{
        PoolProperties p = new PoolProperties();
        p.setUrl("jdbc:sqlite:test.db");
        p.setDriverClassName("org.sqlite.JDBC");
        p.setUsername("root");
        p.setPassword("password");
        p.setJmxEnabled(true);
        p.setTestWhileIdle(false);
        p.setTestOnBorrow(true);
        p.setValidationQuery("SELECT 1");
        p.setTestOnReturn(false);
        p.setValidationInterval(30000);
        p.setTimeBetweenEvictionRunsMillis(30000);
        p.setMaxActive(100);
        p.setInitialSize(10);
        p.setMaxWait(10000);
        p.setRemoveAbandonedTimeout(60);
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(10);
        p.setLogAbandoned(true);
        p.setRemoveAbandoned(true);
        p.setJdbcInterceptors(
                "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
                        "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
        DataSource datasource = new DataSource();
        datasource.setPoolProperties(p);
        return datasource;
    }

    protected <T> RowMapper<T> getSingleColumnRowMapper(Class<T> requiredType) {
        return new SingleColumnRowMapper<T>(requiredType);
    }

    protected RowMapper<Map<String, Object>> getColumnMapRowMapper() {
        return new ColumnMapRowMapper();
    }
}
